In [1]:
import sqlite3 as sq3
import pandas as pd
import numpy as np
import plotly.express as px

con = sq3.connect("opioid.db")
annua = pd.read_sql_query("SELECT * from annual", con)
lan = pd.read_sql_query("SELECT * from land", con)
populatio = pd.read_sql_query("SELECT * from population", con)

# you have to close the connection
con.close

annual=pd.DataFrame(annua)
land=pd.DataFrame(lan)
population=pd.DataFrame(populatio)
population
Out[1]:
BUYER_COUNTY BUYER_STATE countyfips STATE COUNTY county_name NAME variable year population
0 1 AUTAUGA AL 01001 1 1 Autauga Autauga County, Alabama B01003_001 2006 51328
1 2 BALDWIN AL 01003 1 3 Baldwin Baldwin County, Alabama B01003_001 2006 168121
2 3 BARBOUR AL 01005 1 5 Barbour Barbour County, Alabama B01003_001 2006 27861
3 4 BIBB AL 01007 1 7 Bibb Bibb County, Alabama B01003_001 2006 22099
4 5 BLOUNT AL 01009 1 9 Blount Blount County, Alabama B01003_001 2006 55485
... ... ... ... ... ... ... ... ... ... ... ...
28260 28261 WASHAKIE WY 56043 56 43 Washakie Washakie County, Wyoming B01003_001 2014 8444
28261 28262 WESTON WY 56045 56 45 Weston Weston County, Wyoming B01003_001 2014 7135
28262 28263 SKAGWAY AK 02230 2 230 Skagway Skagway Municipality, Alaska B01003_001 2014 996
28263 28264 HOONAH ANGOON AK 02105 2 105 Hoonah Angoon Hoonah-Angoon Census Area, Alaska B01003_001 2014 2126
28264 28265 PETERSBURG AK 02195 2 195 Petersburg Petersburg Borough, Alaska B01003_001 2014 3212

28265 rows × 11 columns

In [2]:
NA=annual.loc[annual.countyfips == 'NA']
NA
Out[2]:
BUYER_COUNTY BUYER_STATE year count DOSAGE_UNIT countyfips
187 188 ADJUNTAS PR 2006 147 102800 NA
188 189 ADJUNTAS PR 2007 153 104800 NA
189 190 ADJUNTAS PR 2008 153 45400 NA
190 191 ADJUNTAS PR 2009 184 54200 NA
191 192 ADJUNTAS PR 2010 190 56200 NA
... ... ... ... ... ... ... ...
27753 27754 NA NV 2007 447 200600 NA
27754 27755 NA NV 2008 5 2200 NA
27755 27756 NA OH 2006 23 5100 NA
27756 27757 NA PR 2006 10 17800 NA
27757 27758 NA PR 2007 2 1300 NA

760 rows × 7 columns

In [3]:
annual.countyfips = np.where((annual.BUYER_COUNTY == 'MONTGOMERY') & (annual.BUYER_STATE == 'AR'),'05097',annual.countyfips)

annual = annual.drop(annual[annual.countyfips == 'NA'].index)
annual
Out[3]:
BUYER_COUNTY BUYER_STATE year count DOSAGE_UNIT countyfips
0 1 ABBEVILLE SC 2006 877 363620 45001
1 2 ABBEVILLE SC 2007 908 402940 45001
2 3 ABBEVILLE SC 2008 871 424590 45001
3 4 ABBEVILLE SC 2009 930 467230 45001
4 5 ABBEVILLE SC 2010 1197 539280 45001
... ... ... ... ... ... ... ...
27736 27737 ZAVALA TX 2010 248 200100 48507
27737 27738 ZAVALA TX 2011 406 244800 48507
27738 27739 ZAVALA TX 2012 473 263700 48507
27739 27740 ZAVALA TX 2013 399 186700 48507
27740 27741 ZAVALA TX 2014 162 148930 48507

27007 rows × 7 columns

In [4]:
land1= land[['Areaname','STCOU','LND110210D']].copy()
land1.rename(columns = {'STCOU':'countyfips'}, inplace = True)
land1
Out[4]:
Areaname countyfips LND110210D
0 UNITED STATES 00000 3531905.43
1 ALABAMA 01000 50645.33
2 Autauga, AL 01001 594.44
3 Baldwin, AL 01003 1589.78
4 Barbour, AL 01005 884.88
... ... ... ...
3193 Sweetwater, WY 56037 10426.65
3194 Teton, WY 56039 3995.38
3195 Uinta, WY 56041 2081.26
3196 Washakie, WY 56043 2238.55
3197 Weston, WY 56045 2398.09

3198 rows × 3 columns

In [5]:
population.merge(land1, on='countyfips', how='left')
Out[5]:
BUYER_COUNTY BUYER_STATE countyfips STATE COUNTY county_name NAME variable year population Areaname LND110210D
0 1 AUTAUGA AL 01001 1 1 Autauga Autauga County, Alabama B01003_001 2006 51328 Autauga, AL 594.44
1 2 BALDWIN AL 01003 1 3 Baldwin Baldwin County, Alabama B01003_001 2006 168121 Baldwin, AL 1589.78
2 3 BARBOUR AL 01005 1 5 Barbour Barbour County, Alabama B01003_001 2006 27861 Barbour, AL 884.88
3 4 BIBB AL 01007 1 7 Bibb Bibb County, Alabama B01003_001 2006 22099 Bibb, AL 622.58
4 5 BLOUNT AL 01009 1 9 Blount Blount County, Alabama B01003_001 2006 55485 Blount, AL 644.78
... ... ... ... ... ... ... ... ... ... ... ... ... ...
28260 28261 WASHAKIE WY 56043 56 43 Washakie Washakie County, Wyoming B01003_001 2014 8444 Washakie, WY 2238.55
28261 28262 WESTON WY 56045 56 45 Weston Weston County, Wyoming B01003_001 2014 7135 Weston, WY 2398.09
28262 28263 SKAGWAY AK 02230 2 230 Skagway Skagway Municipality, Alaska B01003_001 2014 996 Skagway, AK 452.33
28263 28264 HOONAH ANGOON AK 02105 2 105 Hoonah Angoon Hoonah-Angoon Census Area, Alaska B01003_001 2014 2126 Hoonah-Angoon, AK 7524.92
28264 28265 PETERSBURG AK 02195 2 195 Petersburg Petersburg Borough, Alaska B01003_001 2014 3212 Petersburg, AK 3281.98

28265 rows × 13 columns

In [6]:
annual['DOSAGE_UNIT'] = pd.to_numeric(annual['DOSAGE_UNIT'], downcast="float")
annual=annual.groupby('year')['DOSAGE_UNIT'].mean().reset_index()
fig=px.scatter(annual, x='year', y='DOSAGE_UNIT')
fig.write_image("fig2.png")
fig.show()
In [ ]: